import json
from datetime import datetime
from django.http import JsonResponse
from django.shortcuts import render, redirect
from django.views.decorators.csrf import csrf_exempt
# from django.views.decorators.csrf import csrf_exempt
from Util.SQL import *


@csrf_exempt
def bound(request):
    # time是时间轴需要显示的时间节点，在点击时需要获得其对应的其他属性，即nid，bbox等，time不会重复
    startdate = request.POST.get('startdate', '0')
    enddate = request.POST.get('enddate', '0')
    position = request.POST.get('position1', '0')
    current_time = datetime.now().strftime('%Y-%m-%d')
    if len(startdate) == 0 & len(enddate) == 0:  # 处理用户未输入完全日期的情况
        startdate = current_time
        enddate = current_time
    elif len(startdate) == 0:
        startdate = enddate
    elif len(enddate) == 0:
        enddate = startdate
    date = [startdate, enddate]
    sc, boundary = get_location(position, True)  # 获取bbox值
    content = []
    if sc == -1:  # 获取不到bbox值
        sc, boundary = get_location(position, False)  # 获取经纬度
        if sc != -1:  # 获取到经纬度
            ori_string = "\
            SELECT event_id,date,bbox,information,address,location_list,a,b,c,d\
                FROM (\
                    SELECT \
                        event_id,date,bbox,information,address,location_list,\
                        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(bbox, ', ', 1), '(', -1) AS DECIMAL(10, 5)) AS a,\
                        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(bbox, ', ', 2), ', ', -1) AS DECIMAL(10, 5)) AS b,\
                        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(bbox, ', ', 3), ', ', -1) AS DECIMAL(10, 5)) AS c,\
                        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(bbox, ', ', -1), ')', 1) AS DECIMAL(10, 5)) AS d\
                    FROM event\
                ) AS temp\
            WHERE \
                a < @ \
                AND b < # \
                AND c > @ \
                AND d > #\
                AND date BETWEEN '&' AND '~';"
            sql_string = ori_string.replace("@", str(sc['lng'])).replace("#", str(sc['lat'])).replace("&", str(
                date[0])).replace("~", str(date[1]))
            result = select(sql_string)  # 获取符合该经纬度的数据
        else:
            result = []  # 未获取到经纬度，赋初值
    else:
        # start = time.perf_counter()
        ori_string = "\
            SELECT event_id,date,bbox,information,address,location_list,a,b,c,d\
                FROM (\
                    SELECT \
                        event_id,date,bbox,information,address,location_list,\
                        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(bbox, ', ', 1), '(', -1) AS DECIMAL(10, 5)) AS a,\
                        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(bbox, ', ', 2), ', ', -1) AS DECIMAL(10, 5)) AS b,\
                        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(bbox, ', ', 3), ', ', -1) AS DECIMAL(10, 5)) AS c,\
                        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(bbox, ', ', -1), ')', 1) AS DECIMAL(10, 5)) AS d\
                    FROM event\
                ) AS temp\
                WHERE \
                    (c>@ AND a<$) AND (d># AND b<%)\
                    AND date BETWEEN '&' AND '~';"
        sql_string = ori_string.replace("@", str(sc[0])).replace("#", str(sc[1])).replace("$", str(sc[2])).replace(
            "%", str(sc[3])).replace("&", str(date[0])).replace("~", str(date[1]))
        result = select(sql_string)  # 获取符合该bbox的数据
    if len(result) == 0:
        request.session['position1'] = -1
        return redirect('index.html')
    # print(result)
    key_value_lists = {}
    # 遍历字典数组
    for d in result:
        # 遍历每个字典的键值对
        for key, value in d.items():
            # 如果 key 不在 key_value_lists 中，则初始化一个空列表
            if key not in key_value_lists:
                key_value_lists[key] = []
            # 将值添加到对应的键的值列表中
            if key == 'date':
                value = value.strftime("%Y-%m-%d")
            if key == 'a' or key == 'b' or key == 'c' or key == 'd':
                value = float(value)
            key_value_lists[key].append(value)
    coordinates_list = []
    for item in key_value_lists['location_list']:
        tmp = []
        coordinates_str = item[2:-2].split('),(')
        for point in coordinates_str:
            tmp.append([float(point.split(',')[0]), float(point.split(',')[1])])
        coordinates_list.append(tmp)
    print(coordinates_list)
    content = {'event_id': key_value_lists['event_id'], 'date': key_value_lists['date'],
               'bbox': [key_value_lists['a'], key_value_lists['b'], key_value_lists['c'], key_value_lists['d']],
               'information': key_value_lists['information'], 'address': key_value_lists['address'],
               'boundary': coordinates_list}
    return render(request, 'bound.html', content)


@csrf_exempt
def test(request):
    # if request.method == "POST":
    position1 = request.GET.get('position1', '0')  # 读取输入位置
    startdate = request.GET.get('startdate', '0')  # 读取输入开始时间
    enddate = request.GET.get('enddate', '0')  # 读取输入结束时间
    print(position1)
    print(startdate)
    print(enddate)
    return render(request, 'test.html')
